from sqlalchemy import *
from migrate import *
from datetime import datetime
try:
    from hashlib import sha1
except ImportError:
    sys.exit('ImportError: no module named hashlib\nIf you are on python2.4 this library is not part of python. Please install it. Example: easy_install hashlib')
import os

meta = MetaData(migrate_engine)

tg_user = Table('tg_user', meta,
  Column('user_id', Integer, autoincrement=True, primary_key=True),
  Column('user_name', Unicode(16), unique=True, nullable=False),
  Column('email_address', Unicode(255), unique=True, nullable=False),
  Column('display_name', Unicode(255)),
  Column('password', Unicode(80)),
  Column('created', DateTime, default=datetime.now),
  Column('diver_id', Integer(),ForeignKey('Diver.id'))
)
def _set_password( password):
        """Hash password on the fly."""
        hashed_password = password

        if isinstance(password, unicode):
            password_8bit = password.encode('UTF-8')
        else:
            password_8bit = password
        
        salt = sha1()
        salt.update(os.urandom(60))
        hash = sha1()
        hash.update(password_8bit + salt.hexdigest())
        hashed_password = salt.hexdigest() + hash.hexdigest()

        # make sure the hased password is an UTF-8 object at the end of the
        # process because SQLAlchemy _wants_ a unicode object for Unicode columns
        if not isinstance(hashed_password, unicode):
            hashed_password = hashed_password.decode('UTF-8')

        return hashed_password
    
tg_permission = Table('tg_permission', meta,
  Column('permission_id', Integer, autoincrement=True, primary_key=True ),
  Column('permission_name', Unicode(16), unique=True, nullable=False),
  Column('description', Unicode(255)),
)

tg_group = Table('tg_group', meta,
  Column('group_id', Integer, autoincrement=True, primary_key=True),
  Column('group_name', Unicode(16), unique=True, nullable=False),
  Column('display_name', Unicode(255)),
  Column('created', DateTime, default=datetime.now),
)

tg_group_permission = Table('tg_group_permission', meta,
    Column('group_id', Integer, ForeignKey('tg_group.group_id', onupdate="CASCADE", ondelete="CASCADE")),
    Column('permission_id', Integer, ForeignKey('tg_permission.permission_id', onupdate="CASCADE", ondelete="CASCADE"))
)
tg_user_group = Table('tg_user_group', meta,
    Column('user_id', Integer, ForeignKey('tg_user.user_id',
        onupdate="CASCADE", ondelete="CASCADE")),
    Column('group_id', Integer, ForeignKey('tg_group.group_id',
        onupdate="CASCADE", ondelete="CASCADE"))
)

def upgrade():
    migrate_engine.execute(tg_user.insert().values({'user_id': 1,
                                                    'email_address' : u'mk@ediver.org',
                                                    'user_name':u'macio',
                                                    'display_name':u'Maciej Kaniewski',
                                                    'password':_set_password('macio')
                                                    }))
    migrate_engine.execute(tg_permission.insert().values({'permission_id':1,'permission_name':u'manage','description':u'Permission to add new news'}))
    migrate_engine.execute(tg_permission.insert().values({'permission_id':2,'permission_name':u'news_new','description':u'Permission to add new news'}))
    migrate_engine.execute(tg_permission.insert().values({'permission_id':3,'permission_name':u'news_edit','description':u'Permission to edit news'}))
    migrate_engine.execute(tg_permission.insert().values({'permission_id':4,'permission_name':u'news_delete','description':u'Permission to remove news'}))
    migrate_engine.execute(tg_permission.insert().values({'permission_id':5,'permission_name':u'new_profile','description':u'Permission to create new Profile of a diver'}))
    migrate_engine.execute(tg_permission.insert().values({'permission_id':6,'permission_name':u'profile','description':u'Permission to view Profile of a diver'}))
    migrate_engine.execute(tg_permission.insert().values({'permission_id':7,'permission_name':u'logbook','description':u'Permission to view edit log book'}))
    
    migrate_engine.execute(tg_group.insert().values({'group_id':1,'group_name':u'managers','description':u'Managers Group'}))
    migrate_engine.execute(tg_group.insert().values({'group_id':2,'group_name':u'users','description':u'Normal Users'}))
    migrate_engine.execute(tg_group.insert().values({'group_id':3,'group_name':u'new_users','description':u'New Users'}))
    
    migrate_engine.execute(tg_group_permission.insert().values({'group_id':1,'permission_id':1}))
    migrate_engine.execute(tg_group_permission.insert().values({'group_id':1,'permission_id':2}))
    migrate_engine.execute(tg_group_permission.insert().values({'group_id':1,'permission_id':3}))
    migrate_engine.execute(tg_group_permission.insert().values({'group_id':1,'permission_id':4}))
    
    migrate_engine.execute(tg_group_permission.insert().values({'group_id':3,'permission_id':5}))
    migrate_engine.execute(tg_group_permission.insert().values({'group_id':2,'permission_id':7}))
    migrate_engine.execute(tg_group_permission.insert().values({'group_id':2,'permission_id':6}))
    
    migrate_engine.execute(tg_user_group.insert().values({'group_id':1,'user_id':1}))
    migrate_engine.execute(tg_user_group.insert().values({'group_id':3,'user_id':1}))
    

def downgrade():
    
    migrate_engine.execute(tg_group_permission.delete('group_id=1 and permission_id=1'))
    migrate_engine.execute(tg_group_permission.delete('group_id=1 and permission_id=2'))
    migrate_engine.execute(tg_group_permission.delete('group_id=1 and permission_id=3'))
    migrate_engine.execute(tg_group_permission.delete('group_id=1 and permission_id=4'))
    migrate_engine.execute(tg_group_permission.delete('group_id=1 and permission_id=7'))
    
    migrate_engine.execute(tg_group_permission.delete('group_id=3 and permission_id=5'))
    migrate_engine.execute(tg_group_permission.delete('group_id=2 and permission_id=6'))

    
    migrate_engine.execute(tg_user.delete('user_id = 1'))
    
    migrate_engine.execute(tg_user_group.delete('group_id = 1 and user_id =1'))
    migrate_engine.execute(tg_user_group.delete('group_id = 3 and user_id =1'))

    migrate_engine.execute(tg_group.delete('group_id = 1'))
    migrate_engine.execute(tg_group.delete('group_id = 2'))
    migrate_engine.execute(tg_group.delete('group_id = 3'))
    
    migrate_engine.execute(tg_permission.delete('permission_id=1'))
    migrate_engine.execute(tg_permission.delete('permission_id=2'))
    migrate_engine.execute(tg_permission.delete('permission_id=3'))
    migrate_engine.execute(tg_permission.delete('permission_id=4'))
    migrate_engine.execute(tg_permission.delete('permission_id=5'))
    migrate_engine.execute(tg_permission.delete('permission_id=6'))
    migrate_engine.execute(tg_permission.delete('permission_id=7'))
